﻿using GPS.MODELS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GPS.DataAccessLayer;
using AutoMapper;
using PagedList;
using GPS.ENTITYS;
using GPS.COMMON;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.AspNet.Identity;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using System.Web.UI.WebControls;
using System.Web.UI;

namespace GPS.Controllers.GPS_BaoCao
{
    public class GPS_CB_LuuLuongDTController : FDBController
    {
        #region onnit
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
        public GPS.MODELS.ApplicationUser getCurrentUser()
        {
            ApplicationDbContext dbUser = new ApplicationDbContext();
            string userId = User.Identity.GetUserId();
            ApplicationUser _currentUser = dbUser.Users.Find(userId);
            return _currentUser;
        }
        public void initialCategorySearchAction(ViewReportModelGPS_CANHBAO_LLDT SearchModel)
        {
            ViewBag.checkuser = this.getCurrentUser().MA_TINHTP;
            string getdvgtvt_Ma = GPS.DAL.Check_User.CheckUser(this.getCurrentUser().MA_TINHTP, SearchModel.pDVGTVT_MA);
            SearchModel.pDVGTVT_MA = getdvgtvt_Ma;
            ViewBag.lstKhoaHoc = CategoryCommon.DEFAULT_VALUE_DDL;
        }
        #endregion

        #region Index
        public ActionResult Index(ViewReportModelGPS_CANHBAO_LLDT SearchModel, int? currentPage)
        {
            initialCategorySearchAction(SearchModel);
            List<ReportModelGPS_CANHBAO_LLDT> model = new List<ReportModelGPS_CANHBAO_LLDT>();
            if (!string.IsNullOrEmpty(SearchModel.pDVGTVT_MA) && SearchModel.ExportExcel =="Search")
            {
                
            }
            if (SearchModel.ExportExcel == "ExportExcel")
            {
                try
                {
                    model = Getdata(SearchModel);
                    Int16 icount = 1;
                    foreach (var currentEntity in model)
                    {
                        currentEntity.STT = icount;
                        icount++;
                    }
                    ViewBag.TotalRow = model.Count();

                    FileInfo template = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\CB_LuuLuongDaoTao.xlsx"));
                    FileInfo newFile = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\GPS_BaoCaoQuaTocDoGioiHanNEW.xlsx"));
                    using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(newFile, template))
                    {
                        ExcelWorkbook myWorkbook = excelPackage.Workbook;
                        ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];
                        int recordIndex = 4;
                        foreach (var item in model)
                        {
                            myWorksheet.Cells[recordIndex, 1].Value = item.STT.ToString();
                            myWorksheet.Cells[recordIndex, 2].Value = item.LLDT_DVGTVT_MA == null ? "" : item.LLDT_DVGTVT_MA;
                            myWorksheet.Cells[recordIndex, 3].Value = item.DVGTVT_TEN == null ? "" : item.LLDT_DVGTVT_MA;
                            myWorksheet.Cells[recordIndex, 4].Value = item.LLDT_LUULUONG == null ? "" : item.LLDT_LUULUONG;
                            myWorksheet.Cells[recordIndex, 5].Value = item.LLDT_THUCTE == null ? "" : item.LLDT_THUCTE;
                            myWorksheet.Cells[recordIndex, 6].Value = item.LLDT_CHENHLECH == null ? "" : item.LLDT_CHENHLECH;
                            recordIndex++;
                        }
                        string sss = this.getCurrentUser().MA_TINHTP.ToString();
                        myWorksheet.Cells[2, 1].Value = _db.Filter<SDONVIGTVT>(x => x.DVGTVT_MA == sss).Select(x => x.DVGTVT_TEN).FirstOrDefault();
                        excelPackage.Save();
                    }
                    string contentType = string.Empty;
                    string CurrentFileName = Server.MapPath(@"\Templates\GPS_Excel\GPS_BaoCaoQuaTocDoGioiHanNEW.xlsx");
                    if (CurrentFileName.Contains(".xlsx"))
                    {
                        contentType = "application/msexcel";
                    }
                    return File(CurrentFileName, contentType, string.Format("Canhbao_LuuLuongDaoTao_{0}_{1}.xlsx", SearchModel.pDVGTVT_MA, DateTime.Now.ToShortDateString().Replace("/", "_")));
                }
                catch (Exception ex)
                {
                    TempData["Message"] = ex.ToString();
                    return RedirectToAction("Index");
                }
            }
            else
            {
                model = Getdata(SearchModel);

                //var _lst_KhoaHoc = model.GroupBy(x => new { x.LLDT_DVGTVT_MA, x.DVGTVT_TEN })
                //          .Select(cl => new ReportModelGPS_CANHBAO_LLDT
                //          {
                //              LLDT_DVGTVT_MA = cl.First().LLDT_DVGTVT_MA,
                //              DVGTVT_TEN = cl.First().DVGTVT_TEN,
                //              LLDT_LUULUONG = cl.Sum(c => Convert.ToDecimal(c.LLDT_LUULUONG)),
                //          }).ToList();
               

                Int16 icount = 1;
                foreach (var currentEntity in model)
                {
                    currentEntity.STT = icount;
                    icount++;
                }
                ViewBag.TotalRow = model.Count();
                var pageIndex = SearchModel.Page ?? 1;
                SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
                return View(SearchModel);
            }
        }
        #endregion

        #region Search
        public ActionResult Search(ViewReportModelGPS_CANHBAO_LLDT SearchModel, int? currentPage)
        {
            initialCategorySearchAction(SearchModel);
            List<ReportModelGPS_CANHBAO_LLDT> model = new List<ReportModelGPS_CANHBAO_LLDT>();
            if (!string.IsNullOrEmpty(SearchModel.pDVGTVT_MA) && SearchModel.ExportExcel == "Search")
            {

            }
            if (SearchModel.ExportExcel == "ExportExcel")
            {
                try
                {
                    model = GetdataSearch(SearchModel);
                    Int16 icount = 1;
                    foreach (var currentEntity in model)
                    {
                        currentEntity.STT = icount;
                        icount++;
                    }
                    ViewBag.TotalRow = model.Count();

                    FileInfo template = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\CB_LuuLuongDaoTao.xlsx"));
                    FileInfo newFile = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\GPS_BaoCaoQuaTocDoGioiHanNEW.xlsx"));
                    using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(newFile, template))
                    {
                        ExcelWorkbook myWorkbook = excelPackage.Workbook;
                        ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];
                        int recordIndex = 4;
                        foreach (var item in model)
                        {
                            myWorksheet.Cells[recordIndex, 1].Value = item.STT.ToString();
                            myWorksheet.Cells[recordIndex, 2].Value = item.LLDT_DVGTVT_MA == null ? "" : item.LLDT_DVGTVT_MA;
                            myWorksheet.Cells[recordIndex, 3].Value = item.DVGTVT_TEN == null ? "" : item.LLDT_DVGTVT_MA;
                            myWorksheet.Cells[recordIndex, 4].Value = item.LLDT_LUULUONG == null ? "" : item.LLDT_LUULUONG;
                            myWorksheet.Cells[recordIndex, 5].Value = item.LLDT_THUCTE == null ? "" : item.LLDT_THUCTE;
                            myWorksheet.Cells[recordIndex, 6].Value = item.LLDT_CHENHLECH == null ? "" : item.LLDT_CHENHLECH;
                            recordIndex++;
                        }
                        string sss = this.getCurrentUser().MA_TINHTP.ToString();
                        myWorksheet.Cells[2, 1].Value = _db.Filter<SDONVIGTVT>(x => x.DVGTVT_MA == sss).Select(x => x.DVGTVT_TEN).FirstOrDefault();
                        excelPackage.Save();
                    }
                    string contentType = string.Empty;
                    string CurrentFileName = Server.MapPath(@"\Templates\GPS_Excel\GPS_BaoCaoQuaTocDoGioiHanNEW.xlsx");
                    if (CurrentFileName.Contains(".xlsx"))
                    {
                        contentType = "application/msexcel";
                    }
                    return File(CurrentFileName, contentType, string.Format("Canhbao_LuuLuongDaoTao_{0}_{1}.xlsx", SearchModel.pDVGTVT_MA, DateTime.Now.ToShortDateString().Replace("/", "_")));
                }
                catch (Exception ex)
                {
                    TempData["Message"] = ex.ToString();
                    return RedirectToAction("Index");
                }
            }
            else
            {
                model = GetdataSearch(SearchModel);

                //var _lst_KhoaHoc = model.GroupBy(x => new { x.LLDT_DVGTVT_MA, x.DVGTVT_TEN })
                //          .Select(cl => new ReportModelGPS_CANHBAO_LLDT
                //          {
                //              LLDT_DVGTVT_MA = cl.First().LLDT_DVGTVT_MA,
                //              DVGTVT_TEN = cl.First().DVGTVT_TEN,
                //              LLDT_LUULUONG = cl.Sum(c => Convert.ToDecimal(c.LLDT_LUULUONG)),
                //          }).ToList();


                Int16 icount = 1;
                foreach (var currentEntity in model)
                {
                    currentEntity.STT = icount;
                    icount++;
                }
                ViewBag.TotalRow = model.Count();
                var pageIndex = SearchModel.Page ?? 1;
                SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
                return View(SearchModel);
            }
        }
        #endregion

        public List<ReportModelGPS_CANHBAO_LLDT> GetdataSearch(ViewReportModelGPS_CANHBAO_LLDT SearchModel)
        {
            var param2 = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (SearchModel.pDVGTVT_MA == null ? DBNull.Value : (object)SearchModel.pDVGTVT_MA), ParameterDirection.Input);
            var pLLDT_TUNGAY = new OracleParameter("pLLDT_TUNGAY", OracleDbType.Date, (SearchModel.pLLDT_TUNGAY == null ? DBNull.Value : (object)SearchModel.pLLDT_TUNGAY), ParameterDirection.Input);
            var pLLDT_DENNGAY = new OracleParameter("pLLDT_DENNGAY", OracleDbType.Date, (SearchModel.pLLDT_DENNGAY == null ? DBNull.Value : (object)SearchModel.pLLDT_DENNGAY), ParameterDirection.Input);
            var param3 = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<ReportModelGPS_CANHBAO_LLDT>("BEGIN GPS_CANHBAO.GPS_CANHBAO_LLDT_2(:pDVGTVT_MA,:pLLDT_TUNGAY,:pLLDT_DENNGAY,:pRET); end;",
             param2, pLLDT_TUNGAY, pLLDT_DENNGAY, param3).ToList();
            return entities;
        }


        public List<ReportModelGPS_CANHBAO_LLDT> Getdata(ViewReportModelGPS_CANHBAO_LLDT SearchModel)
        {
            var param2 = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (SearchModel.pDVGTVT_MA == null ? DBNull.Value : (object)SearchModel.pDVGTVT_MA), ParameterDirection.Input);
            var pLLDT_TUNGAY = new OracleParameter("pLLDT_TUNGAY", OracleDbType.Date, (SearchModel.pLLDT_TUNGAY == null ? DBNull.Value : (object)SearchModel.pLLDT_TUNGAY), ParameterDirection.Input);
            var pLLDT_DENNGAY = new OracleParameter("pLLDT_DENNGAY", OracleDbType.Date, (SearchModel.pLLDT_DENNGAY == null ? DBNull.Value : (object)SearchModel.pLLDT_DENNGAY), ParameterDirection.Input);
            var param3 = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<ReportModelGPS_CANHBAO_LLDT>("BEGIN GPS_CANHBAO.GPS_CANHBAO_LLDT(:pDVGTVT_MA,:pLLDT_TUNGAY,:pLLDT_DENNGAY,:pRET); end;",
             param2,pLLDT_TUNGAY,pLLDT_DENNGAY, param3).ToList();
            return entities;
        }


        public ActionResult ExportToExcelSearch(ViewReportModelGPS_CANHBAO_LLDT SearchModel)
        {
            GridView gv = new GridView();
            DataTable tbl = GPS.COMMON.Helpers.MapListToDataTable<ReportModelGPS_CANHBAO_LLDT>(GetdataSearch(SearchModel));
            gv.DataSource = GetdataSearch(SearchModel);
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=BaoCaoLuuLuongDaoTao.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
            return RedirectToAction("Index");
        }

        public ActionResult ExportToExcel(ViewReportModelGPS_CANHBAO_LLDT SearchModel)
        {
            GridView gv = new GridView();
            DataTable tbl = GPS.COMMON.Helpers.MapListToDataTable<ReportModelGPS_CANHBAO_LLDT>(Getdata(SearchModel));
            gv.DataSource = Getdata(SearchModel);
            gv.DataBind();
            Response.ClearContent();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment; filename=BaoCaoLuuLuongDaoTao.xls");
            Response.ContentType = "application/ms-excel";
            Response.Charset = "";
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
            return RedirectToAction("Index");
        }
    }
}